package com.zmh.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zmh.common.DateUtil;
import com.zmh.common.OperationUtil;
import com.zmh.model.book.BookInfo;
import com.zmh.model.book.CatalogInfo;

/** 
 * 章节操作
 * @author zhoumh
 * @version 创建时间：2017年7月28日 下午4:47:07 
 * @Description 
*/
@Repository
public class CatalogDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 查询一本书的全部章节信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:35:15
	 */
	public List<CatalogInfo> queryCatalogByAll(String bid){
		String sqlstr = "SELECT id, uuid, bid, name, content, count, utime, sort, cstatus, type, state, createtime, updatetime, description FROM cataloginfo WHERE bid = ?";
		List<CatalogInfo> catalogs = new ArrayList<>();
		Object[] param = new Object[]{bid};
		catalogs = jdbcTemplate.query(sqlstr, param, new RowMapper<CatalogInfo>(){
				@Override
				public CatalogInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		return catalogs;
	}
	/**
	 * 根据UUID查询章节信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月17日 下午5:19:45
	 */
	public CatalogInfo queryCatalogByUuid(String uuid){
		String sqlstr = "SELECT id, uuid, bid, name, content, count, utime, sort, cstatus, type, state, createtime, updatetime, description FROM cataloginfo WHERE uuid=?";
		CatalogInfo catalog = new CatalogInfo();
		Object[] param = new Object[]{uuid};
		catalog = jdbcTemplate.queryForObject(sqlstr, param, new RowMapper<CatalogInfo>(){
				@Override
				public CatalogInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		return catalog;
	}
	/**
	 * 根据是否已发送通知查询章节信息
	 * @param uuid
	 * @return: CatalogInfo
	 * @author: zhoumh
	 * @version: 创建时间：2017年11月27日 下午4:51:07 
	 * @Description:
	 */
	public List<CatalogInfo> selCatalogByCstatus(String cstatus){
		String sqlstr = "SELECT id, uuid, bid, name, content, count, utime, sort, cstatus, type, state, createtime, updatetime, description FROM cataloginfo WHERE cstatus=?";
		List<CatalogInfo> catalogs = new ArrayList<>();
		Object[] param = new Object[]{cstatus};
		catalogs = jdbcTemplate.query(sqlstr, param, new RowMapper<CatalogInfo>(){
				@Override
				public CatalogInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		return catalogs;
	}
	/**
	 * 根据书籍ID和章节名称查询章节是否已存在
	 * @param bid 书籍ID
	 * @param name 章节名称
	 * @return: count 章节数量 
	 * @author: zhoumh
	 * @version: 创建时间：2017年11月16日 下午7:25:04 
	 * @Description: 注意description是书籍目录源站赋予的id，不保证不重复
	 */
	public int sumCatalogByName(CatalogInfo catalogInfo){
		String sqlstr = "SELECT count(id) FROM cataloginfo WHERE description=? AND name=? AND bid=?";
		int count = 0;
		Object[] param = new Object[]{catalogInfo.getDescription(),catalogInfo.getName(),catalogInfo.getBid()};
		count = jdbcTemplate.queryForObject(sqlstr, param, Integer.class);
		return count;
	}
	
	/**
	 * 根据时间查询数量
	 * @param catalogInfo
	 * @return 
	 * @return: int
	 * @author: zhoumh
	 * @version: 创建时间：2017年12月1日 下午3:13:44 
	 * @Description:
	 */
	public int sumCatalogByCreatetime(String createtime){
		String sqlstr = "SELECT count(id) FROM cataloginfo WHERE createtime > ? ";
		int count = 0;
		Object[] param = new Object[]{createtime};
		count = jdbcTemplate.queryForObject(sqlstr, param, Integer.class);
		return count;
	}
	//增加
	public int insertCatalog(CatalogInfo catalog){
		String sqlstr = "insert into cataloginfo (uuid, bid, name, content, count, utime, sort, type, description) values(?,?,?,?,?,?,?,?,?)";
		Object[] param = new Object[]{OperationUtil.getUUID(),catalog.getBid(),catalog.getName(),catalog.getContent(),catalog.getCount(),catalog.getUtime(),catalog.getSort(),catalog.getType(),catalog.getDescription()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
		
	}
	
	/**
	 * 增加的时候判断数据是否已存在，已存在就不增加
	 * @param catalog
	 * @return: int
	 * @author: zhoumh
	 * @version: 创建时间：2017年11月18日 下午2:44:46 
	 * @Description:
	 */
	public int insCatalogByExi(CatalogInfo catalog){
		String sqlstr = "insert into cataloginfo (uuid, bid, name, content, count, utime, sort, type, description) SELECT ?,?,?,?,?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT id FROM cataloginfo WHERE description=? AND name=? AND bid=?)";
		Object[] param = new Object[]{OperationUtil.getUUID(),catalog.getBid(),catalog.getName(),catalog.getContent(),catalog.getCount(),catalog.getUtime(),catalog.getSort(),catalog.getType(),catalog.getDescription(),catalog.getDescription(),catalog.getName(),catalog.getBid()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
		
	}
	
	//修改
	public int updateCatalog(CatalogInfo catalog){
		String sqlstr = "update cataloginfo set bid=?, name=?, content=?, count=?, utime=?, sort=?, cstatus=?, type=?, state=?, updatetime=?, description=? where uuid=?";
		Object[] param = new Object[]{catalog.getBid(),catalog.getName(),catalog.getContent(),catalog.getCount(),catalog.getUtime(),catalog.getSort(),catalog.getCstatus(),catalog.getType(),catalog.getState(),DateUtil.getDateTime(),catalog.getDescription(),catalog.getUuid()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	//删除
	public int deleteCatalogByUuid(String uuid){
		String sqlstr = "delete from cataloginfo where uuid = ?";
		Object[] param = new Object[]{uuid};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/*
	 * 数据库查询结果映射
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:36:14
	 */
	public CatalogInfo objSet(ResultSet rs, int rowNum) throws SQLException {
		CatalogInfo catalog = new CatalogInfo();
		catalog.setId(rs.getInt("id"));
		catalog.setUuid(rs.getString("uuid"));
		catalog.setBid(rs.getString("bid"));
		catalog.setName(rs.getString("name"));
		catalog.setContent(rs.getString("content"));
		catalog.setCount(rs.getInt("count"));
		catalog.setUtime(rs.getString("utime"));
		catalog.setSort(rs.getInt("sort"));
		catalog.setCstatus(rs.getString("cstatus"));
		catalog.setType(rs.getString("type"));
		catalog.setState(rs.getString("state"));
		catalog.setCreateTime(rs.getString("createtime"));
		catalog.setUpdateTime(rs.getString("updatetime"));
		catalog.setDescription(rs.getString("description"));
		return catalog;
	}
	
}
